<?php

class DemandController extends BaseController {
   
    

    public function getClientbyId() {

        $_cits=DB::table("h_saveclientid")->get();


        $refData = $this->refData();
        $clients = Client::select( 
                              "name" , "recommended" ,  
                              "importance" , "nationality" ,

                              "english_name" ,"sex" , "customer_source" ,
                              "birthday" , "gifts" , "id_number" , 
                              "usually_contact" , "usually_start_time" , "usually_end_time" , 
                              "usually_contact1" , "usually_start_time1" , "usually_end_time1" , 
                              "usually_contact2" , "usually_start_time2" , "usually_end_time2" , 
                              "holidays_contact" , "holidays_start_time" , "holidays_end_time" , 
                              "holidays_contact1" , "holidays_start_time1" , "holidays_end_time1" , 
                              "holidays_contact2" , "holidays_start_time2" , "holidays_end_time2" , 
                              "background_and_features" ,


                              "mobile" ,"send_sms" ,
                              "mobile1" ,"send_sms1" ,
                              "mobile2" , "send_sms2" , 
                              "home_phone" , 
                              "home_phone1" , 
                              "home_phone2" , 
                              "company_phone" , "company_phone1" ,"company_phone2" , 
                              "company_phone_ext" ,  
                              "company_phone_ext1" , 
                              "company_phone_ext2" ,
                               "email" , "send_email" , 
                               "email1" , "send_email1" , 
                               "email2" , "send_email2" , 
                               "fax" , "fax1" , "fax2" , 
                               "home_building_name" , "home_send_publication" , "home_address" , 
                               "home_building_name1" , "home_send_publication1" , "home_address1" , 
                               "home_building_name2" , "home_send_publication2" , "home_address2" ,
                               "company_name" , "type_of_company" , "unicode" , "department" ,
                                "title" , "category" , 

                              "company_address" , "company_building_name" , 
                              "company_send_publication" , "company_website" , 

                              "company_name1" , "type_of_company1" , "unicode1" , 
                              "department1" , "title1" , "category1" , 
                              "company_address1" , "company_building_name1" , "company_send_publication1" ,
                              "company_website1" , 

                               "company_name2" , "type_of_company2" , "unicode2" , 
                               "department2" , "title2" , 
                               "category2" , "company_address2" , "company_building_name2" , 
                               "company_send_publication2" , "company_website2" , 
                               
                               "front_man" , "head" , 
                                "broker" 
                                );//->get()->toArray();
      foreach($_cits as $_c){

          $clients->orwhere("client_id","=",$_c->client_id);

      }
      $clients=$clients->get()->toArray();

        $d = array();
        $titles = array("客戶姓名", "特徵", "重要性", "國籍", 
                        "英文姓名", "性別", "客戶來源", 

                        "生日", "送禮", "身份證字號", 

                        "平常聯絡", "開始時間", "結束時間", 
                        "平常聯絡1", "開始時間1", "結束時間1", 
                        "平常聯絡2", "開始時間2", "結束時間2", 
                        "假日聯絡", "結束時間", "開始時間", 
                        "假日聯絡1", "開始時間1", "結束時間1", 
                        "假日聯絡2", "開始時間2", "結束時間2", 

                        "背景與特色",

                        "行動電話", "簡訊發送", 
                        "行動電話1", "簡訊發送1", 
                        "行動電話2", "簡訊發送2", 
                        "住家電話", "住家電話1", 
                        "住家電話2", 
                        "公司電話", 
                        "公司電話1", "公司電話2", 
                        "分機", "分機1", "分機2", 
                        "電子郵件", "寄發EDM", 
                        "電子郵件1", "寄發EDM1", 
                        "電子郵件2", "寄發EDM2", 
                        "傳真", "傳真1", "傳真2", 
                        "住家地址", "大樓名稱", "住家寄發刊物", 
                        "住家地址1", "大樓名稱1", "住家寄發刊物1", 
                        "住家地址2", "大樓名稱2", "住家寄發刊物2", 
                        "公司名稱", "公司類型", "統一編號", "部門", 
                        "職稱", "類別", 

                        "公司地址", "大樓名稱", 
                        "公司寄發刊物", "公司網址",

                         "公司名稱1", "公司類型1", "統一編號1", 
                         "部門1", "職稱1", "類別1", 
                         "公司地址1", "大樓名稱1", "公司寄發刊物1", 
                        "公司網址1", 

                        "公司名稱2", "公司類型2", "統一編號2", 
                        "部門2", "職稱2", "類別2", 
                        "公司地址2", "大樓名稱2", 
                        "公司寄發刊物2", "公司網址2", 

                        "接待人", "主管", 
                         "經紀人");
        $icount = 1;
        $d[0]=$titles; 
        
        foreach ($clients as $aa => $bb) {

            foreach ($bb as $a => $b) {
              
                

                if($a=="sex"){

                  $sex='';
                  if($b==0){
                    $sex='女';
                    $d[$icount][$a] = $sex;
                  }elseif($b==1){
                    $sex='男';
                    $d[$icount][$a] = $sex;
                  }else{
                    $d[$icount][$a] = $b;
                  }

                  

                }elseif($a=='nationality'){
                  
                  foreach($refData['h_nationality'] as $h_nationality){
                    // echo $h_nationality->nationality_id;
                    // echo "/".$b.'';
                    // echo '<br>';
                    if(!empty($b)){

                      if($b==$h_nationality->nationality_id){
                        //echo $h_nationality->value;
                        $d[$icount][$a] = $h_nationality->value;
                      }

                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }

                }elseif($a=='customer_source'){
                  
                  foreach($refData['h_customer_source'] as $h_customer_source){
                    if(!empty($b)){
                      if($b==$h_customer_source->customer_source_id){                      
                        $d[$icount][$a] = $h_customer_source->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='type_of_company'){
                  
                  foreach($refData['h_type_of_company'] as $h_type_of_company){
                    if(!empty($b)){
                      if($b==$h_type_of_company->type_of_company_id){                      
                        $d[$icount][$a] = $h_type_of_company->value;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }  
                  }
                }elseif($a=='type_of_company1'){
                  
                  foreach($refData['h_type_of_company'] as $h_type_of_company){
                    if(!empty($b)){
                      if($b==$h_type_of_company->type_of_company_id){                      
                        $d[$icount][$a] = $h_type_of_company->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='type_of_company2'){
                  
                  foreach($refData['h_type_of_company'] as $h_type_of_company){
                    if(!empty($b)){
                      if($b==$h_type_of_company->type_of_company_id){                      
                        $d[$icount][$a] = $h_type_of_company->value;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                }elseif($a=='broker'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }  
                  }

                }elseif($a=='head'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                  
                }elseif($a=='front_man'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      }
                    }else{
                      $d[$icount][$a] = $b;
                    }
                  }
                  
                }elseif($a=='original_broker'){
                  
                  foreach($refData['h_users'] as $h_users){
                    if(!empty($b)){
                      if($b==$h_users->users_id){                      
                        $d[$icount][$a] = $h_users->name;
                      } 
                    }else{
                      $d[$icount][$a] = $b;
                    } 
                  }
                  
                }else{

                  
                    $d[$icount][$a] = $b;
                  

                }
            }
            
            $icount++;
        }
        //$d[0]='';  
        DB::table("h_saveclientid")->truncate();
        return $d;
    }

    public function defaultDemandSelect(){

      $demand = Demand::select( 
        "client_contact_id"  , "client_id" , "users_id" , "point_count" , 
        "shop_point_id" , "type_rent" , "type_sell" , "type_help_seal" , 
        "type_consignment" , "ms" , "mr" , "object_usage" , "name" , 
        "mobile" , "home_phone" , "company_phone" , "home_address" , 
        "building_name" , "company_address" , "company_name" , "email" , 
        "contact" , "contact_phone" , "building_area1" , "building_area2" , "demand_area" , "parking_number" , "acceptable_mechanical" , "b1" , "b2" , "indoor_area1" , "indoor_area2" , "indoor_area" , "other_demand" , "land_area1" , "land_area2" , "key_areas1" , "key_areas2" , "specify_building1" , "specify_building2" , "sections1" , "sections2" , "object_name1" , "object_name2" , "mrt_line1" , "mrt_start1" , "mrt_until1" , "mrt_line2" , "mrt_start2" , "mrt_until2" , "range_east" , "range_north" , "range_west" , "range_south" , "park1" , "park2" , "school1" , "school2" , "circle1" , "circle2" , "administrative_area1" , "administrative_area2" , "total_budget1" , "total_budget2" , "budget_per_ping1" , "budget_per_ping2" , "reported_rate1" , "reported_rate2" , "leasing_business_purposes" , "year_of_construction" , "object_case_type" , "windows" , "windows1" , "windows2" , "facing_the_household" , "facing_the_household1" , "facing_the_household2" , "building_facing" , "building_facing1" , "building_facing2" , "seat1" , "seat2" , "towards1" , "towards2" , "households_demand" , "recommended_floor" , "designated_floors1" , "designated_floors2" , "floors" , "object_styles" , "public_facilities" , "accessories_with" , "management" , "number_of_rooms1" , "number_of_rooms2" , "number_of_hall1" , "number_of_hall2" , "number_of_toilet1" , "number_of_toilet2" , "number_of_suite1" , "number_of_suite2" , "partition_the" , "floor_height" , "customer_source" , "source_magazine" , "source_other" , "negotiate_mode1" , "negotiate_mode2" , "key_considerations" , "key_considerations_other" , "background_and_characteristics" , "dazhi" , "rooms" , "suite" , "adult" , "child" , "background_and_characteristics_other" , "opinion_floor1" , "opinion_money1" , "opinion_to_money1" , "opinion_floor2" , "opinion_money2" , "opinion_to_money2" , "total_budget" , "other_total_budget" , "required_level" , "remark" , "provide_information" , "provide_information_other" , "tracking_project" , "competent_suggestions" , "number" , "negotiate_year1" , "negotiate_year2" , "negotiate_month1" , "negotiate_month2" , "negotiate_day1" , "negotiate_day2" , "negotiate_time1" , "negotiate_time2" , "negotiate2" , "currency" , "area_unit" , "status" , "invalid_description" , "completed_by" , "front_man" , "head" , "original_broker" , "broker");
        //->get()->toArray();
        return $demand; 

    }


    public function getDemandData() {


        //$refData = $this->refData();

        $clients = $this->defaultDemandSelect();

        $clients=$clients->get()->toArray();
        $d = array();
        
        $icount = 1;
        //$d[0]=$titles; 
        
        foreach ($clients as $aa => $bb) {

            foreach ($bb as $a => $b) {              
                $d[$icount][$a] = $b;
            }
            
            $icount++;
        }         
        return $d;
    }

    public function getDemandExcelAll() {

        
        $data = $this->getDemandData();

       

        Excel::create(date("ymd_his"), function($excel) use($data) {

            $excel->sheet('Sheetname', function($sheet) use($data) {

                $sheet->fromArray($data);
            });
        })->export('xls');
    }


    public function postClientId(){
      
      $_pa=Input::all();
      //print_r($_pa['id']);
      $_ids=explode(',', $_pa['id']);
      
      foreach ($_ids as $key => $id) {

           DB::table("h_saveclientid")->insert( array('client_id'=>$id) );
      
      }
      //echo $_pa["id"];
      //h_saveclientid
    }
    public function getClientExcelById(){

      //$_pa=Input::all();

      //print_r($_pa);


        $data = $this->getClientbyId();
        
        Excel::create(date("ymd_his"), function($excel) use($data) {

            $excel->sheet('Sheetname', function($sheet) use($data) {

                $sheet->fromArray($data);
            });
        })->export('xls');


    }




    public function refData(){
      $data=array();

      $h_nationality=DB::table("h_nationality")->paginate(100)->toArray();
      $data["h_nationality"] =$h_nationality["data"];

      $h_customer_source=DB::table("h_customer_source")->paginate(100)->toArray();
      $data["h_customer_source"] =$h_customer_source["data"];      

      $h_type_of_company=DB::table("h_type_of_company")->paginate(100)->toArray();
      $data["h_type_of_company"] =$h_type_of_company["data"];

      $h_users=DB::table("h_users")->paginate(100)->toArray();
      $data["h_users"] =$h_users["data"];

      return $data;
      //print_r($data);

    }



    public function demandHeadTitle(){
      
    }

}
